package hotel;

import java.io.*;
import java.net.ConnectException;
import java.net.Socket;
import java.net.SocketException;
import java.net.UnknownHostException;
import java.security.InvalidKeyException;
import java.security.Key;
import java.security.KeyFactory;
import java.security.NoSuchAlgorithmException;
import java.security.PublicKey;
import java.security.SecureRandom;
import java.security.spec.InvalidKeySpecException;
import java.security.spec.X509EncodedKeySpec;
import java.sql.*;
import java.util.*;

import javax.crypto.BadPaddingException;
import javax.crypto.Cipher;
import javax.crypto.CipherInputStream;
import javax.crypto.CipherOutputStream;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.KeyGenerator;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.ShortBufferException;
import javax.swing.*;

import com.sun.rowset.CachedRowSetImpl;

import crypto.GeneraChiavi;

public class Database {
	private Connection conn;
	private Statement stat;
	static ImageIcon iconax = new ImageIcon("Risorse/iconax.png");
	private boolean connesso = false;
	private Socket s;
	private ObjectOutputStream out = null;
	private ObjectInputStream in;
	private int codice;
	private PublicKey serverPublicKey;
	private Key chiaveDes;
	private CipherInputStream cis;

	public Database() throws SQLException, IOException {
		
	}

	public int effettuaLogin(String username,String password) throws IOException, InvalidKeyException, NoSuchAlgorithmException, NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException ,NullPointerException{
		if(!connesso) connetti();
		out.writeInt(77);
		criptaEInvia(username);
		criptaEInvia(password);
		int log=in.readInt();
		return log;
	}
	
	public void chiudiSocket() {
		try {
			if (!connesso)
				return;
			out.writeInt(99);
			out.flush();
			byte[] m=riceviEDecripta();
			JOptionPane.showMessageDialog(null, new String(m));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		}
	}

	public void connetti() {
		try {
			s = new Socket("localhost", 47624);
			out = new ObjectOutputStream(s.getOutputStream());
			in = new ObjectInputStream(s.getInputStream());
			scambiaChiavi();
			connesso = true;
		} catch (ConnectException e){
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (UnknownHostException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (IOException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (InvalidKeyException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (NoSuchAlgorithmException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (InvalidKeySpecException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (NoSuchPaddingException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (IllegalBlockSizeException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		} catch (BadPaddingException e) {
			JOptionPane.showMessageDialog(null,
					"Connessione al Server non riuscita!");
			connesso=false;
		}
	}

	private void scambiaChiavi() throws NoSuchAlgorithmException, IOException, InvalidKeySpecException, ClassNotFoundException, NoSuchPaddingException, InvalidKeyException, IllegalBlockSizeException, BadPaddingException {
		out.writeInt(88);
		out.flush();
		byte[] publicKeyBytes= new byte[1000];
		int numByte = in.read( publicKeyBytes );
		publicKeyBytes = Arrays.copyOf( publicKeyBytes, numByte );
		X509EncodedKeySpec ks = new X509EncodedKeySpec(publicKeyBytes); 
		KeyFactory kf = KeyFactory.getInstance("RSA");
		serverPublicKey= kf.generatePublic(ks);
		KeyGenerator gen = KeyGenerator.getInstance("DES");
		gen.init(new SecureRandom());
		chiaveDes = gen.generateKey();
		Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
		cipher.init(Cipher.ENCRYPT_MODE, serverPublicKey);
		out.write( cipher.doFinal(chiaveDes.getEncoded()));
		out.flush();

	}

	public boolean registra(String username, String password, String email,
			String telefono) {
		try{
		if (!connesso)
			connetti();
		try{
		out.writeInt(3);
		out.flush();
		criptaEInvia(username);
		codice = in.readInt();
		if (codice == 2) {
			JOptionPane.showMessageDialog(null, "Username Esistente!");
			return false;
		} else {
			criptaEInvia(email);
			byte[] m=riceviEDecripta();
			String str = new String(m);
			if (str.equals("SI")) {
				JOptionPane.showMessageDialog(null, "Email esistente!");
				return false;
			} else {
				criptaEInvia(password);
				criptaEInvia(telefono);
				byte[] m1=riceviEDecripta();
				String mex = new String(m1);
				JOptionPane.showMessageDialog(null, mex);
				return true;
			}
		}
		}catch(IOException e){
			return false;
		}catch(NullPointerException e){
			return false;
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		}
		}catch(NullPointerException e){
			
		}return false;
	}

	public boolean inviaCodice(String username, String password, String email,
			String telefono, String codice) {
		try{
		out.writeInt(4);
		out.flush();
		criptaEInvia(codice);
		byte[] m=riceviEDecripta();
		String mex = new String(m);
		JOptionPane.showMessageDialog(null, mex);
		byte[] m1=riceviEDecripta();
		String bo=new String(m1);
		if (bo.equals("true"))
			return true;
		return false;
		}catch(NullPointerException e){
			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		}return false;
	}

	public boolean recuperaPassword(String username, String email)
			throws IOException, InvalidKeyException, NoSuchAlgorithmException, NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException {
		if (!connesso)
			connetti();
		out.writeInt(5);
		out.flush();
		criptaEInvia(username);
		criptaEInvia(email);
		byte[] m=riceviEDecripta();
		String mex = new String(m);
		JOptionPane.showMessageDialog(null, mex);
		byte[] m1=riceviEDecripta();
		String bo=new String(m1);
		if (bo.equals("true"))
			return true;
		return false;
	}
	
	public void criptaEInvia(String s) throws NoSuchAlgorithmException, NoSuchPaddingException, InvalidKeyException, IllegalBlockSizeException, BadPaddingException, IOException{
		try{
			byte[] messaggio=s.getBytes();
			Cipher cifra = Cipher.getInstance("DES");
			cifra.init(Cipher.ENCRYPT_MODE, chiaveDes);
			out.write(cifra.doFinal(messaggio));
			out.flush();
		}catch(SocketException e){
			JOptionPane.showMessageDialog(null,"Si e' verificato un errore con il server.\n Il programma verra' terminato");
			System.exit(-1);
		}
	}
	
	public byte[] riceviEDecripta() throws IllegalBlockSizeException, BadPaddingException, IOException, InvalidKeyException, NoSuchAlgorithmException, NoSuchPaddingException{
		try{
			Cipher cifra = Cipher.getInstance("DES");
			cifra.init(Cipher.DECRYPT_MODE, chiaveDes);
			byte[] rec=new byte[20000];
			int num=in.read(rec);
			rec=Arrays.copyOf(rec,num);
			byte[] messaggio=cifra.doFinal(rec);
			return messaggio;
		}catch(NegativeArraySizeException e){
			JOptionPane.showMessageDialog(null,"Si e' verificato un errore con il server.\n Il programma verra' terminato");
			System.exit(-1);
		}return null;
	}
	
	public boolean recuperaUsername(String password,String email) throws IOException, InvalidKeyException, NoSuchAlgorithmException, NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException{
		if(!connesso) connetti();
		out.writeInt(6);
		out.flush();
		criptaEInvia(password);
		criptaEInvia(email);
		byte[] m1=riceviEDecripta();
		String mex=new String(m1);
		byte[] m2=riceviEDecripta();
		String bo=new String(m2);
		JOptionPane.showMessageDialog(null, mex);
		if (bo.equals("true"))
			return true;
		return false;
	}

	public MyResultSet querySelect(String query) {
		if (!connesso){
			connetti();
		}
		try{
			out.writeInt(2);
			out.flush();
			criptaEInvia(query);
			MyResultSet rs=new MyResultSet();
			byte[] messaggio=riceviEDecripta();
			String s=new String(messaggio);
			while(!s.equals("EOF")){
				StringTokenizer st=new StringTokenizer(s,"#");
				int len=st.countTokens();
				String[] arr=new String[len];
				for(int i=0;i<len;i++){
					arr[i]=st.nextToken();
				}
				rs.add(arr);
				messaggio=riceviEDecripta();
				s=new String(messaggio);
			}
			return rs;
		}catch(IOException e){
			e.printStackTrace();
		}catch(NullPointerException e){
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		}return null;
	}

	public int queryInserimento(String query) {
		try{
			if (!connesso)
				connetti();
			out.writeInt(1);
			out.flush();
			criptaEInvia(query);
			int codice=in.readInt();
			return codice;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		}
		return codice;
	}
	
	public int queryUpdate(String query)  {
		try{
		if (!connesso)
			connetti();
		out.writeInt(1);
		out.flush();
		criptaEInvia(query);
		int codice = in.readInt();
		return codice;
		}catch(NullPointerException e){
			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		}
		return codice;
	}

	public void iniz(String username) {
		String schema="CREATE SCHEMA "+username;
		String clienti = "CREATE TABLE "+username+".CLIENTI ("
				+ "codice INT AUTO_INCREMENT," + "nome VARCHAR(20),"
				+ "cognome VARCHAR(20)," + "datadinascita DATE,"
				+ "comunedinascita VARCHAR(20),"
				+ "provinciadinascita VARCHAR(2)," + "residenza VARCHAR(20),"
				+ "provinciadiresidenza VARCHAR(2),"
				+ "tipodidocumento VARCHAR(20),"
				+ "numerodidocumento VARCHAR(15),"
				+ "rilasciatoda VARCHAR(30)," + "datadirilascio DATE,"
				+ "datadiarrivo DATE," + "indirizzo VARCHAR(100),"
				+ "email VARCHAR(30)," + "presente BOOLEAN DEFAULT 0,"
				+ "PRIMARY KEY(codice)," + "UNIQUE(numerodidocumento))";
		String camere = "CREATE TABLE "+username+".CAMERE (" + "codice INT AUTO_INCREMENT,"
				+ "numero VARCHAR(10)," + "numeroposti INT,"
				+ "numpostioccupati INT," + "matrimoniale BOOLEAN,"
				+ "stato VARCHAR(20)," + "prezzo VARCHAR(8),"
				+ "PRIMARY KEY(codice)," + "UNIQUE(numero))";
		String associazione = "CREATE TABLE "+username+".ASSOCIAZIONE("
				+ "numerodocumento VARCHAR(15),"
				+ "camera VARCHAR(10),"
				+ "prezzo VARCHAR(8),"
				+ "datadipartenza DATE,"
				+ "PRIMARY KEY (numerodocumento,camera),"
				+ "UNIQUE(numerodocumento),"
				+ "FOREIGN KEY(numerodocumento) REFERENCES CLIENTI(numerodidocumento),"
				+ "FOREIGN KEY(camera) REFERENCES CAMERE(numero))";
		String storico = "CREATE TABLE "+username+".STORICO("
				+ "numerodocumento VARCHAR(15),"
				+ "numcamera VARCHAR(10),"
				+ "dataarrivo DATE,"
				+ "datapartenza DATE,"
				+ "prezzo VARCHAR(5),"
				+ "PRIMARY KEY (numerodocumento,dataarrivo),"
				+ "FOREIGN KEY(numerodocumento) REFERENCES CLIENTI(numerodidocumento),"
				+ "FOREIGN KEY(numcamera) REFERENCES CAMERE(numero))";
		String prenotazioni = "CREATE TABLE "+username+".PRENOTAZIONI("
				+ "camera VARCHAR(10)," + "dal DATE," + "al DATE,"
				+ "nominativo VARCHAR(20)," + "PRIMARY KEY (camera,dal,al),"
				+ "FOREIGN KEY (camera) REFERENCES CAMERE(numero))";
		queryInserimento(schema);
		queryInserimento(clienti);
		queryInserimento(camere);
		queryInserimento(associazione);
		queryInserimento(storico);
		queryInserimento(prenotazioni);
	}

	public void chiudiConnessione() throws SQLException{
		conn.close();
	}

	public boolean rimuoviCliente(String numeroDocumento,String username) {
		boolean fatto = false;
		queryInserimento("DELETE FROM "+username+".STORICO WHERE numerodocumento='"
				+ numeroDocumento + "'");
		queryInserimento("DELETE FROM "+username+".CLIENTI WHERE numerodidocumento='"
				+ numeroDocumento + "'");
		fatto = true;
		return fatto;
	}

	public boolean rimuoviCamera(String numeroCamera,String username) {
		int err=queryInserimento("DELETE FROM "+username+".prenotazioni WHERE camera='"
				+ numeroCamera + "'");
		int err1=queryInserimento("DELETE FROM "+username+".STORICO WHERE numcamera='" + numeroCamera
				+ "'");
		int err2=queryInserimento("DELETE FROM "+username+".CAMERE WHERE numero='" + numeroCamera
				+ "'");
		if (err == 1451 || err1==1451 || err2==1451) {
			JOptionPane.showMessageDialog(null,
					"Stanza al momento occupata!", "Attenzione",
					JOptionPane.WARNING_MESSAGE, iconax);
		}else
			return true;
		return false;
	}

	public MyResultSet cercaPerCognome(String cognome,String username) throws SQLException, ClassNotFoundException, InvalidKeyException, NoSuchAlgorithmException, NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException {
		return querySelect("SELECT * INTO "+username+".CLIENTI WHERE cognome='"
				+ cognome + "'");
	}

	public MyResultSet cercaQuery(String query) {
		return querySelect(query);
	}

	public MyResultSet cercaPerNome(String nome,String username) {
		return querySelect("SELECT * INTO "+username+".CLIENTI WHERE nome='" + nome
				+ "'");
	}

	public MyResultSet cercaPerNumeroDocumento(String documento,String username){
		return querySelect("SELECT * INTO "+username+".CLIENTI WHERE numerodidocumento='"
						+ documento + "'");
	}

	public Connection getConnection() throws SQLException, IOException {
		Properties props = new Properties();
		FileInputStream in = new FileInputStream("Risorse/database.properties");
		props.load(in);
		in.close();
		String drivers = props.getProperty("jdbc.drivers");
		String url = props.getProperty("jdbc.url");
		String username = props.getProperty("jdbc.username");
		String password = props.getProperty("jdbc.password");
		if (drivers != null)
			System.setProperty("jdbc.drivers", drivers);
		return DriverManager.getConnection(url, username, password);
	}

	public boolean rimuoviPrenotazione(String camera, String dal, String al,String username) {
		boolean fatto = false;
		String dal1 = dal.substring(6) + "/" + dal.substring(3, 5) + "/"
				+ dal.substring(0, 2);
		String al1 = al.substring(6) + "/" + al.substring(3, 5) + "/"
				+ al.substring(0, 2);
		String query = "DELETE FROM "+username+".prenotazioni WHERE camera='" + camera
				+ "' && dal='" + dal1 + "' && al='" + al1 + "'";
		queryInserimento(query);
		fatto = true;
		return fatto;
	}

}
